﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Streaming_Service.SQL_Ting {
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using System.Diagnostics;
    using System.IO;
    using System.IO.Compression;
    using System.Reflection;
    using System.Runtime.Serialization;
    using System.Text;
    using System.Web.UI;
    using System.Xml;
    using System.Xml.Serialization;

    using Streaming_Service.Enum;
    using Streaming_Service.Media;
    using Streaming_Service.User;

    public class MovieQueries {
        public static List<MovieEntry> ListMovies(SqlHandler sh) {
            var SQL = "SELECT mov.*, gen.id AS genreid, gen.name AS genrename FROM movie AS mov "
                      + "LEFT JOIN moviegenre AS mg ON mov.id = mg.movieid "
                      + "LEFT JOIN genre AS gen ON gen.id = mg.genreid";

            var movies = new Dictionary<int, MovieEntry>();

            var c = sh.CreateCommand(SQL);

            var r = c.ExecuteReader();
            while (r.Read()) {
                var id = r.GetInt32(r.GetOrdinal("id"));
                if (!movies.ContainsKey(id)) {
                    movies.Add(id, GetMovieEntry(r));
                }

                if (!r.IsDBNull(r.GetOrdinal("genreid"))) {
                    movies[id].Genre.Add(
                        new GenreEntry
                            { Id = r.GetInt32(r.GetOrdinal("genreid")), Name = r.GetString(r.GetOrdinal("genrename")) });
                }
            }
            r.Close();

            return movies.Values.ToList();
        }

        public static MovieEntry GetMovie(int movieId, SqlHandler sh) {
            var SQL = "SELECT mov.*, gen.id AS genreid, gen.name AS genrename FROM movie AS mov "
                      + "LEFT JOIN moviegenre AS mg ON mov.id = mg.movieid "
                      + "LEFT JOIN genre AS gen ON gen.id = mg.genreid " 
                      + "WHERE mov.id = @mid";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("mid", SqlDbType.Int).Value = movieId;

            var mEntry = new MovieEntry();

            var r = c.ExecuteReader();
            while (r.Read()) {
                if (mEntry.Id == -1) mEntry = GetMovieEntry(r);

                if (!r.IsDBNull(r.GetOrdinal("genreid"))) {
                    mEntry.Genre.Add(
                        new GenreEntry
                            { Id = r.GetInt32(r.GetOrdinal("genreid")), Name = r.GetString(r.GetOrdinal("genrename")) });
                }
            }

            return (mEntry.Id > -1 ? mEntry : null);
        }

        public static MovieEntry GetMovieEntry(SqlDataReader r) {
            var me = new MovieEntry();
            try {
                me.Id = r.GetInt32(r.GetOrdinal("id"));
                me.Title = r.GetString(r.GetOrdinal("title"));
                me.Year = r.GetInt16(r.GetOrdinal("movieyear"));
                me.Language = r.GetString(r.GetOrdinal("movielanguage"));
                me.HighDefinition = (r.GetByte(r.GetOrdinal("hd")) != 0 ? XBool.True : XBool.False);
                me.Genre = new List<GenreEntry>();
                me.ParentalGuidance = EnumHelper.GetParentalGuidance(r.GetString(r.GetOrdinal("pg")));
                me.Length = r.GetInt16(r.GetOrdinal("movielength"));
                me.ReleaseDate = r.GetDateTime(r.GetOrdinal("releasedate"));
                me.HdPrice = PricingQueries.GetHdPrice(me.ReleaseDate);
                me.SdPrice = PricingQueries.GetSdPrice(me.ReleaseDate);
                me.Summary = r.GetString(r.GetOrdinal("summary"));
                me.ImdbId = r.GetString(r.GetOrdinal("imdb"));
            } catch { }
            return me;
        }

        public static int InsertMovie(MovieEntry entry, SqlHandler sh) {
            if (!ProperMovieEntry(entry)) return -1;
            if (MovieExists(entry.Title, entry.Year, sh) || MovieExistsImdb(entry.ImdbId, sh)) return -1;

            var SQL = "INSERT INTO movie (title, movieyear, movielength, movielanguage, pg, summary, hd, releasedate, imdb) " +
                "VALUES(@title, @year, @length, @language, @pg, @summary, @hd, @releasedate, @imdb);" +
                "SELECT CAST(SCOPE_IDENTITY() AS INT)";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("title", SqlDbType.VarChar).Value = entry.Title;
            c.Parameters.Add("year", SqlDbType.Int).Value = entry.Year;
            c.Parameters.Add("length", SqlDbType.Int).Value = entry.Length;
            c.Parameters.Add("language", SqlDbType.VarChar).Value = entry.Language;
            c.Parameters.Add("pg", SqlDbType.VarChar).Value = entry.ParentalGuidance.ToString();
            c.Parameters.Add("summary", SqlDbType.Text).Value = (entry.Summary ?? "");
            c.Parameters.Add("hd", SqlDbType.TinyInt).Value = entry.HighDefinition;
            c.Parameters.Add("releasedate", SqlDbType.DateTime).Value = entry.ReleaseDate;
            c.Parameters.Add("imdb", SqlDbType.VarChar).Value = entry.ImdbId;

            var r = c.ExecuteScalar();
            if(r is int) {
                var id = (int)r;
                var theId = id > 0 ? id : LatestId(sh);

                if(theId != -1) MediaFunc.CreateMovieFolder(theId);

                return theId;
            }
            return -1;
        }

        public static bool UpdateMovie(int movieId, MovieEntry entry, SqlHandler sh) {
            var updVars = new Dictionary<string, QuickField>();

            if (!string.IsNullOrEmpty(entry.Title)) {
                updVars["title"] = new QuickField(entry.Title, "title = @title", SqlDbType.VarChar);
            }

            if (MediaFunc.ValidYear(entry.Year)) {
                updVars["year"] = new QuickField(entry.Year, "movieyear = @year", SqlDbType.Int);
            }

            if (MediaFunc.ValidLength(entry.Length)) {
                updVars["length"] = new QuickField(entry.Length, "movielength = @length", SqlDbType.Int);
            }

            if (!string.IsNullOrEmpty(entry.Language)) {
                updVars["language"] = new QuickField(entry.Language, "movielanguage = @language", SqlDbType.VarChar);
            }

            if (entry.ParentalGuidance != ParentalGuidance.Ignore) {
                updVars["pg"] = new QuickField(entry.ParentalGuidance.ToString(), "pg = @pg", SqlDbType.VarChar);
            }

            if (!string.IsNullOrEmpty(entry.Summary)) {
                updVars["summary"] = new QuickField(entry.Summary, "summary = @summary", SqlDbType.Text);
            }

            if(entry.HighDefinition != XBool.Ignore) {
                updVars["hd"] = new QuickField(entry.HighDefinition == XBool.True ? 1 : 0, "hd = @hd", SqlDbType.TinyInt);
            }

            if(MediaFunc.ValidReleaseDate(entry.ReleaseDate)) {
                updVars["releasedate"] = new QuickField(entry.ReleaseDate, "releasedate = @releasedate", SqlDbType.DateTime);
            }

            if(!string.IsNullOrEmpty(entry.ImdbId)) {
                updVars["imdb"] = new QuickField(entry.ImdbId, "imdb = @imdb", SqlDbType.VarChar);
            }

            if (updVars.Count > 0) {
                var SQL = new StringBuilder("UPDATE movie SET ");
                foreach (var updVar in updVars) {
                    SQL.Append(updVar.Value.SQL + ", ");
                }
                SQL.Remove(SQL.Length - 2, 1);
                SQL.Append("WHERE id = @mid");

                var c = sh.CreateCommand(SQL.ToString());
                c.Parameters.Add("mid", SqlDbType.Int).Value = movieId;
                foreach (var updVar in updVars) {
                    c.Parameters.Add(updVar.Key, updVar.Value.DbType).Value = updVar.Value.Value;
                }

                var r = c.ExecuteNonQuery();

                return r > 0;
            }
            return false;
        }

        public static bool DeleteMovie(int movieId, SqlHandler sh) {
            if (!MovieExists(movieId, sh)) return false;

            var SQL = "DELETE FROM movie WHERE id = @mid";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("mid", SqlDbType.Int).Value = movieId;

            var r = c.ExecuteNonQuery();
            
            if(r > 0) {
                MediaFunc.DeleteMovieFolders(movieId);
                return true;
            }

            return false;
        }

        public static bool ProperMovieEntry(MovieEntry entry) {
            if (string.IsNullOrEmpty(entry.Title)) return false;
            if (!MediaFunc.ValidYear(entry.Year)) return false;
            if (!MediaFunc.ValidLength(entry.Length)) return false;
            if (string.IsNullOrEmpty(entry.Language)) return false;
            if (entry.ParentalGuidance == ParentalGuidance.Ignore) return false;
            if (entry.HighDefinition == XBool.Ignore) return false;
            if (!MediaFunc.ValidReleaseDate(entry.ReleaseDate)) return false;
            if (string.IsNullOrEmpty(entry.ImdbId)) return false;
            return true;
        }

        public static List<String> MovieLanguages(SqlHandler sh) {
            var SQL = "SELECT movielanguage FROM movie";

            var langs = new HashSet<String>();

            var c = sh.CreateCommand(SQL);
            var r = c.ExecuteReader();
            while (r.Read()) {
                var lang = r.GetString(r.GetOrdinal("movielanguage"));
                if (!langs.Contains(lang)) langs.Add(lang);
            }
            r.Close();

            return langs.ToList();
        } 

        public static List<MovieEntry> SearchMovies(MovieSearchEntry searchEntry, SqlHandler sh) {
            var entry = searchEntry.MovieEntry;

            var updVars = new Dictionary<string, QuickField>();

            if (!string.IsNullOrEmpty(entry.Title)) {
                if(searchEntry.TitleIsPartial) {
                    updVars["title"] = new QuickField(entry.Title, "title LIKE '%' + @title + '%'", SqlDbType.VarChar);
                } else {
                    updVars["title"] = new QuickField(entry.Title, "title = @title", SqlDbType.VarChar);
                }
            }

            if (MediaFunc.ValidYear(entry.Year)) {
                if (searchEntry.YearDifference > 0) {
                    if(searchEntry.YearDifferenceDirection == DifferenceDirection.Both ) {
                        var yearHigh = entry.Year + searchEntry.YearDifference;
                        var yearLow = entry.Year - searchEntry.YearDifference;
                        updVars["year"] = new QuickField(entry.Year, "(movieyear = @year OR (movieyear >= " + yearLow + " AND movieyear <=" + yearHigh + "))", SqlDbType.Int);
                    }else if(searchEntry.YearDifferenceDirection == DifferenceDirection.Lower) {
                        var yearNew = entry.Year - searchEntry.YearDifference;
                        updVars["year"] = new QuickField(entry.Year, "movieyear BETWEEN " + yearNew + " AND @year", SqlDbType.Int);
                    }else {
                        var yearNew = entry.Year + searchEntry.YearDifference;
                        updVars["year"] = new QuickField(entry.Year, "movieyear BETWEEN @year AND " + yearNew, SqlDbType.Int);
                    }
                } else {
                    updVars["year"] = new QuickField(entry.Year, "movieyear = @year", SqlDbType.Int);
                }
            }

            if (MediaFunc.ValidLength(entry.Length)) {
                if (searchEntry.LengthDifference > 0) {
                    if (searchEntry.LengthDifferenceDirection == DifferenceDirection.Both) {
                        var lengthHigh = entry.Length + searchEntry.LengthDifference;
                        var lengthLow = entry.Length - searchEntry.LengthDifference;
                        updVars["length"] = new QuickField(entry.Length, "(movielength = @length OR (movielength >= " + lengthLow + " AND movielength <=" + lengthHigh + "))", SqlDbType.Int);
                    } else if (searchEntry.YearDifferenceDirection == DifferenceDirection.Lower) {
                        var lengthNew = entry.Length - searchEntry.LengthDifference;
                        updVars["length"] = new QuickField(entry.Length, "movielength BETWEEN " + lengthNew + " AND @length", SqlDbType.Int);
                    } else {
                        var lengthNew = entry.Length + searchEntry.LengthDifference;
                        updVars["length"] = new QuickField(entry.Length, "movielength BETWEEN @length AND " + lengthNew, SqlDbType.Int);
                    }
                } else {
                    updVars["length"] = new QuickField(entry.Year, "movielength = @length", SqlDbType.Int);
                }
            }

            if (!string.IsNullOrEmpty(entry.Language)) {
                updVars["language"] = new QuickField(entry.Language, "movielanguage = @language", SqlDbType.VarChar);
            }

            if (entry.ParentalGuidance != ParentalGuidance.Ignore) {
                updVars["pg"] = new QuickField(entry.ParentalGuidance.ToString(), "pg = @pg", SqlDbType.VarChar);
            }

            if (entry.HighDefinition != XBool.Ignore) {
                updVars["hd"] = new QuickField(entry.HighDefinition == XBool.True ? 1 : 0, "hd = @hd", SqlDbType.TinyInt);
            }

            if (MediaFunc.ValidReleaseDate(entry.ReleaseDate)) {
                if (searchEntry.ReleaseDateSpecification == DateSpecification.Complete) {
                    updVars["releasedate"] = new QuickField(
                        entry.ReleaseDate, "releasedate = @releasedate", SqlDbType.DateTime);
                }else if(searchEntry.ReleaseDateSpecification == DateSpecification.YearMonth) {
                    updVars["releasedate"] = new QuickField(
                        entry.ReleaseDate, "(YEAR(releasedate) = YEAR(@releasedate) AND MONTH(releasedate) = MONTH(@releasedate))", SqlDbType.DateTime);
                } else {
                    updVars["releasedate"] = new QuickField(
                        entry.ReleaseDate, "YEAR(releasedate) = YEAR(@releasedate)", SqlDbType.DateTime);
                }
            }

            if(!string.IsNullOrEmpty(entry.ImdbId)) {
                updVars["imdb"] = new QuickField(entry.ImdbId, "imdb = @imdb", SqlDbType.VarChar);
            }

            if (updVars.Count > 0) {
                var andor = (searchEntry.AllMustMatch ? "AND" : "OR");

                var  SQL = new StringBuilder();
                SQL.Append("SELECT mov.*, gen.id AS genreid, gen.name AS genrename FROM movie AS mov "
                      + "LEFT JOIN moviegenre AS mg ON mov.id = mg.movieid "
                      + "LEFT JOIN genre AS gen ON gen.id = mg.genreid "
                      + "WHERE");

                foreach (var updVar in updVars) {
                    SQL.Append(" " + updVar.Value.SQL + " " + andor);
                }
                SQL.Remove(SQL.Length - andor.Length - 1, andor.Length + 1);

                var c = sh.CreateCommand(SQL.ToString());
                foreach (var updVar in updVars) {
                    c.Parameters.Add(updVar.Key, updVar.Value.DbType).Value = updVar.Value.Value;
                }

                var movies = new Dictionary<int, MovieEntry>();

                var r = c.ExecuteReader();

                while (r.Read()) {
                    var id = r.GetInt32(r.GetOrdinal("id"));
                    if (!movies.ContainsKey(id)) {
                        movies.Add(id, GetMovieEntry(r));
                    }

                    if (!r.IsDBNull(r.GetOrdinal("genreid"))) {
                        movies[id].Genre.Add(
                            new GenreEntry { Id = r.GetInt32(r.GetOrdinal("genreid")), Name = r.GetString(r.GetOrdinal("genrename")) });
                    }
                }
                r.Close();

                return movies.Values.ToList();
            }

            return new List<MovieEntry>();
        } 

        public static bool MovieExists(int movieId, SqlHandler sh) {
            var SQL = "SELECT id FROM movie WHERE id = @mid";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("mid", SqlDbType.Int).Value = movieId;

            var r = c.ExecuteScalar();

            if(r is int) {
                return (int)r == movieId;
            }

            return false;
        }

        public static bool MovieExists(string title, int year, SqlHandler sh) {
            if (string.IsNullOrEmpty(title)) return false;
            var SQL = "SELECT id FROM movie WHERE title = @title AND movieyear = @year";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("title", SqlDbType.VarChar).Value = title;
            c.Parameters.Add("year", SqlDbType.Int).Value = year;

            var r = c.ExecuteScalar();

            if (r is int) {
                return (int)r > 0;
            }

            return false;
        }

        public static bool MovieExistsImdb(string imdbId, SqlHandler sh) {
            var SQL = "SELECT id FROM movie WHERE imdb = @imdb";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("imdb", SqlDbType.VarChar).Value = imdbId;

            var r = c.ExecuteScalar();

            if (r is int) {
                return (int)r > 0;
            }

            return false;
        }

        public static int MovieCount(SqlHandler sh) {
            var SQL = "SELECT COUNT(*) FROM movie";

            var c = sh.CreateCommand(SQL);

            var r = c.ExecuteScalar();
            if(r is int) {
                return (int)r;
            }
            return 0;
        }

        public static int LatestId(SqlHandler sh) {
            var SQL = "SELECT TOP 1 id FROM movie ORDER BY id DESC";

            var c = sh.CreateCommand(SQL);

            var r = c.ExecuteScalar();
            if(r is int) {
                return (int)r;
            }
            return -1;
        }

        public static List<MovieSearchEntry> ListAllSearches(int userid, int numResults, SqlHandler sh) {
            var SQL = "";
            if(userid < 0) {
                SQL = "SELECT TOP " + (numResults > 0 ? numResults : 1) + " * FROM searchhistory ORDER BY dateofsearch DESC";
            } else {
                SQL = "SELECT TOP " + (numResults > 0 ? numResults : 1) + " * FROM searchhistory WHERE userid = @userid ORDER BY dateofsearch DESC";
            }

            var c = sh.CreateCommand(SQL);

            if(userid > -1) {
                c.Parameters.Add("userid", SqlDbType.Int).Value = userid;
            }

            var movies = new List<MovieSearchEntry>();

            var deserializer = new XmlSerializer(typeof(MovieSearchEntry));
            var r = c.ExecuteReader();
            while(r.Read()) {
                using (var st = new MemoryStream(r.GetSqlBinary(r.GetOrdinal("searched")).Value)) {
                    using (var g = new GZipStream(st, CompressionMode.Decompress)) {
                        var movie = (MovieSearchEntry)deserializer.Deserialize(g);
                        movies.Add(movie);
                    }
                }
            }

            r.Close();

            return movies;
        }

        public static bool InsertSearch(MovieSearchEntry movie, SqlHandler sh) {
            if (movie == null || !UserQueries.UserExists(movie.UserID, sh)) return false;
            var SQL = "INSERT INTO SearchHistory (userid, searched, dateofsearch) VALUES(@userid, @searched, @dateofsearch)";

            var c = sh.CreateCommand(SQL);
            using (var stream = new MemoryStream()) {
                var x = new XmlSerializer(typeof(MovieSearchEntry));
                using (var g = new GZipStream(stream, CompressionMode.Compress)) {
                    x.Serialize(g, movie);
                }

                c.Parameters.Add("userid", SqlDbType.Int).Value = movie.UserID > 0 ? movie.UserID : 0;
                c.Parameters.Add("searched", SqlDbType.VarBinary).Value = stream.ToArray();
                c.Parameters.Add("dateofsearch", SqlDbType.DateTime).Value = DateTime.Now;
            }

            var r = c.ExecuteNonQuery();

            return r > 0;
        }
    }
}